import os
import re
import shutil
import pandas as pd
from tkinter import Tk, filedialog, messagebox, simpledialog, Listbox, MULTIPLE, END, Button, Label, Frame, Text, \
    Scrollbar
from fuzzywuzzy import fuzz  # 需要安装：pip install fuzzywuzzy python-Levenshtein

# 直接在最终路径下匹配文件名，包含文件和文件夹

# 支持的扩展名列表
VALID_EXTENSIONS = ['.xls', '.xlsx', '.doc', '.docx', '.pdf', '.csv', '.wps', '.jpg','.et']

# 需要去除的地点标志列表（可根据实际情况扩展）
LOCATION_KEYWORDS = ["年", "年度", "大圩镇", "合肥市", "街道", "宣城市", "宣州区", "宁国市", "包河区", "埇桥区",
                     "朱桥乡", "砀山县", "宿州市", "芜湖路",
                     "镇西村", "韩池孜社区", "砀城镇", "朱仙庄镇", "沈福村", "沱河街道", "周寨镇", "安徽省", "长丰县",
                     "镇园区", "（", "）", "甲路镇",
                     "吴山镇", "庄墓镇", "2025", "2024", "统计表", "汇总表", "统计表", "登记总表", "评分表", "登记",
                     "台账", "表", "申报表", "清单",
                     "清册表", "申请表", "进度表", "自查表", "记录表", "花名册", "审核确认表", "（第一期）", "（第一批）",
                     "问题清单", "（工业）（建筑业）（房地产业）（服务业商贸业）","（街镇）", "（区级）", "（不含迁坟）","居委会疫苗","附件"]


def select_excel_file():
    """选择Excel文件并返回路径"""
    root = Tk()
    root.withdraw()
    file_path = filedialog.askopenfilename(
        title="选择Excel文件",
        filetypes=[("Excel文件", "*.xls *.xlsx")]
    )
    root.destroy()
    return file_path


def get_sheets(excel_path):
    """获取Excel中的所有sheet名称"""
    return pd.ExcelFile(excel_path).sheet_names


def select_sheets(sheets, exclude=None):
    """让用户选择要处理的sheet，排除指定的sheet"""
    if exclude is None:
        exclude = []

    root = Tk()
    root.title("选择Sheet")

    Label(root, text="请选择要处理的sheet（可多选）:").pack(padx=10, pady=5)
    listbox = Listbox(root, selectmode=MULTIPLE, width=50, height=15)
    for sheet in sheets:
        if sheet not in exclude:
            listbox.insert(END, sheet)
    listbox.pack(padx=10, pady=5)

    selected_sheets = []

    def on_confirm():
        nonlocal selected_sheets
        selected_sheets = [listbox.get(i) for i in listbox.curselection()]
        root.destroy()

    Button(root, text="确认", command=on_confirm).pack(pady=10)
    root.mainloop()
    return selected_sheets


def select_target_folder():
    """选择目标文件夹"""
    root = Tk()
    root.withdraw()
    folder = filedialog.askdirectory(title="选择保存文件的目标文件夹")
    root.destroy()
    return folder


def remove_location_keywords(text):
    """去除文本中的地点关键词"""
    for keyword in LOCATION_KEYWORDS:
        text = text.replace(keyword, "")
    return text


def clean_report_name(text):
    """清理报表名称：去除地点关键词、数字和标点，只保留汉字"""
    # 去除地点关键词
    cleaned = remove_location_keywords(text)
    # 移除非汉字字符（保留汉字）
    cleaned = re.sub(r'[^\u4e00-\u9fff]', '', cleaned)
    return cleaned


def clean_file_name(text):
    """清理文件名：去除数字和标点，只保留汉字"""
    # 移除非汉字字符（保留汉字）
    cleaned = re.sub(r'[^\u4e00-\u9fff]', '', text)
    return cleaned


def calculate_char_match_ratio(report_chars, filename_str):
    """计算字符匹配比例"""
    if not report_chars:
        return 0.0

    # 创建filename字符串的可变字符列表
    filename_chars = list(filename_str)
    matched_count = 0

    for char in report_chars:
        if char in filename_chars:
            matched_count += 1
            # 从列表中移除匹配的字符（避免重复匹配）
            filename_chars.remove(char)

    return matched_count / len(report_chars)


def is_number_only_name(name):
    """检查名称是否只包含数字（可能还有扩展名）"""
    # 移除扩展名
    name_without_ext = os.path.splitext(name)[0]
    # 检查是否只包含数字
    return name_without_ext.isdigit()


def find_first_entry(source_path, report_name, serial_number):
    """在给定路径中查找第一个匹配的文件或文件夹"""
    # 将原表序号转换为字符串（用于匹配）
    serial_str = str(serial_number).strip() if pd.notna(serial_number) else ""

    # 清理报表名称：去除地点关键词、数字和标点，只保留汉字
    cleaned_report = clean_report_name(report_name)
    if not cleaned_report:
        cleaned_report = report_name

    # 将报表名称拆分为单个汉字
    report_chars = list(cleaned_report)

    # 检查源路径是否存在
    if not os.path.exists(source_path):
        print(f"路径不存在: {source_path}")
        return None

    # 获取路径中的所有条目（文件和文件夹）
    try:
        entries = os.listdir(source_path)
    except Exception as e:
        print(f"无法读取路径内容: {source_path}, 错误: {e}")
        return None

    # 第一步：优先匹配原表序号
    if serial_str:
        serial_matches = []
        for entry in entries:
            entry_path = os.path.join(source_path, entry)

            # 检查条目名称是否包含原表序号
            if serial_str in entry:
                print(f"序号匹配成功: '{entry}' (包含序号: {serial_str})")
                serial_matches.append(entry_path)

        # 如果在序号匹配中找到条目，进一步使用汉字匹配筛选
        if serial_matches:
            best_serial_match = None
            highest_serial_score = 0

            for match in serial_matches:
                entry_name = os.path.basename(match)
                # 如果是文件，去掉扩展名
                if os.path.isfile(match):
                    entry_name = os.path.splitext(entry_name)[0]

                cleaned_entry_name = clean_file_name(entry_name)

                # 计算字符匹配比例
                char_match_ratio = calculate_char_match_ratio(report_chars, cleaned_entry_name)

                # 使用模糊匹配分数
                similarity = fuzz.partial_ratio(cleaned_report, cleaned_entry_name)

                # 综合评分（可以调整权重）
                total_score = char_match_ratio * 0.7 + similarity / 100 * 0.3

                print(
                    f"序号匹配后汉字筛选: '{report_name}' -> '{entry_name}', 字符匹配率: {char_match_ratio:.2%}, 模糊匹配: {similarity}%, 总分: {total_score:.2f}")

                if total_score > highest_serial_score:
                    highest_serial_score = total_score
                    best_serial_match = match

            # 如果序号匹配的条目中有汉字匹配度高的，直接返回
            if highest_serial_score > 0.5:
                print(f"序号+汉字最佳匹配: {best_serial_match} (分数: {highest_serial_score:.2f})")
                return best_serial_match

            # 如果序号匹配但汉字匹配度不够高，记录最佳序号匹配，但继续全局汉字匹配
            print(f"序号匹配但汉字匹配度不足 (分数: {highest_serial_score:.2f})，继续全局匹配")

    # 第二步：全局纯汉字匹配（无论是否有序号匹配）
    best_global_match = None
    highest_global_score = 0

    for entry in entries:
        entry_path = os.path.join(source_path, entry)
        entry_name = os.path.basename(entry_path)

        # 如果是文件，检查扩展名
        if os.path.isfile(entry_path):
            ext = os.path.splitext(entry_name)[1].lower()
            if ext not in VALID_EXTENSIONS:
                continue
            # 去掉扩展名
            entry_name = os.path.splitext(entry_name)[0]

        # 清理条目名称：去除数字和标点，只保留汉字
        cleaned_entry_name = clean_file_name(entry_name)

        # 计算字符匹配比例
        char_match_ratio = calculate_char_match_ratio(report_chars, cleaned_entry_name)

        # 使用模糊匹配分数
        similarity = fuzz.partial_ratio(cleaned_report, cleaned_entry_name)

        # 检查核心词序列是否按顺序出现
        sequential_match = True
        last_index = -1
        for char in cleaned_report:
            current_index = cleaned_entry_name.find(char, last_index + 1)
            if current_index == -1:
                sequential_match = False
                break
            last_index = current_index

        # 综合评分
        total_score = char_match_ratio * 0.7 + similarity / 100 * 0.3
        if sequential_match:
            total_score += 0.2  # 顺序匹配加分

        # 调试信息
        debug_info = (
            f"全局匹配检测: '{report_name}' (序号: {serial_str}) -> '{entry_name}'\n"
            f"清理后报表: '{cleaned_report}' (字符: {report_chars})\n"
            f"清理后条目: '{cleaned_entry_name}'\n"
            f"字符匹配率: {char_match_ratio:.2%}, 模糊匹配: {similarity}%, "
            f"顺序匹配: {sequential_match}, 总分: {total_score:.2f}"
        )
        print(debug_info)

        # 放宽字符匹配率阈值
        if char_match_ratio >= 0.8:
            print(f"字符匹配成功 (匹配率: {char_match_ratio:.2%})")
            return entry_path

        # 放宽模糊匹配阈值
        if similarity >= 75:
            print(f"模糊匹配成功 (相似度: {similarity}%)")
            return entry_path

        # 更新最佳匹配
        if total_score > highest_global_score:
            highest_global_score = total_score
            best_global_match = entry_path

    # 降低全局匹配的阈值
    if highest_global_score > 0.4:
        print(f"全局最佳匹配: {best_global_match} (分数: {highest_global_score:.2f})")
        return best_global_match

    # 第三步：如果以上都没有匹配到，且有序号，则匹配只包含序号的条目（忽略汉字）
    if serial_str:
        print(f"尝试匹配只包含序号的条目: {serial_str}")
        for entry in entries:
            entry_path = os.path.join(source_path, entry)
            entry_name = os.path.basename(entry_path)

            # 检查条目名称是否只包含数字（可能是序号）
            if is_number_only_name(entry_name):
                # 检查条目名称中的数字是否与序号匹配
                name_without_ext = os.path.splitext(entry_name)[0]
                if name_without_ext == serial_str:
                    print(f"找到只包含序号的条目: {entry_name} (匹配序号: {serial_str})")
                    return entry_path

            # 检查条目名称是否包含序号（即使有其他字符）
            if serial_str in entry_name:
                # 进一步检查条目名称中是否几乎没有汉字
                cleaned_entry_name = clean_file_name(entry_name)
                if len(cleaned_entry_name) <= 2:  # 如果汉字很少，认为是序号条目
                    print(
                        f"找到以序号为主的条目: {entry_name} (包含序号: {serial_str}, 汉字数量: {len(cleaned_entry_name)})")
                    return entry_path

    # 没有找到匹配条目
    print(f"未匹配: '{report_name}' (序号: {serial_str}, 清理后: '{cleaned_report}')")
    return None


def load_mapping(excel_path, mapping_sheet="Sheet2"):
    """加载映射表，返回映射字典"""
    try:
        # 尝试读取映射表
        mapping_df = pd.read_excel(excel_path, sheet_name=mapping_sheet)

        # 检查必要的列
        if "原始区划" not in mapping_df.columns or "映射路径" not in mapping_df.columns:
            messagebox.showwarning("映射表格式错误",
                                   f"映射表 '{mapping_sheet}' 缺少必要的列('原始区划'或'映射路径')")
            return {}

        # 创建映射字典
        mapping_dict = {}
        for _, row in mapping_df.iterrows():
            original = str(row["原始区划"]).strip()
            mapped = str(row["映射路径"]).strip()
            if original and mapped:
                mapping_dict[original] = mapped
                print(f"映射: '{original}' -> '{mapped}'")

        return mapping_dict

    except Exception as e:
        messagebox.showinfo("映射表加载", f"未找到或无法加载映射表 '{mapping_sheet}': {str(e)}")
        return {}


def apply_mapping(source_path, mapping_dict):
    """应用映射到源路径"""
    # 尝试精确匹配
    if source_path in mapping_dict:
        return mapping_dict[source_path]

    # 尝试部分匹配（处理路径前缀）
    for original, mapped in mapping_dict.items():
        if source_path.startswith(original):
            # 替换路径前缀
            return source_path.replace(original, mapped, 1)

    # 没有匹配的映射，返回原路径
    return source_path


def show_unmatched_report(unmatched_reports):
    """显示未匹配到的报表名称"""
    if not unmatched_reports:
        return

    root = Tk()
    root.title("未匹配报表列表")
    root.geometry("600x400")

    Label(root, text="以下报表未找到匹配文件:", font=("Arial", 12, "bold")).pack(pady=10)

    # 创建文本框和滚动条
    text_frame = Frame(root)
    text_frame.pack(fill="both", expand=True, padx=10, pady=5)

    scrollbar = Scrollbar(text_frame)
    scrollbar.pack(side="right", fill="y")

    text_area = Text(text_frame, wrap="word", yscrollcommand=scrollbar.set)
    text_area.pack(fill="both", expand=True)
    scrollbar.config(command=text_area.yview)

    # 添加未匹配报表名称
    for report in unmatched_reports:
        text_area.insert("end", f"- {report}\n")

    text_area.config(state="disabled")

    Button(root, text="关闭", command=root.destroy).pack(pady=10)
    root.mainloop()


def copy_entry(source_path, target_path):
    """复制文件或文件夹到目标路径"""
    try:
        if os.path.isfile(source_path):
            shutil.copy2(source_path, target_path)
            print(f"已复制文件: {source_path} -> {target_path}")
        elif os.path.isdir(source_path):
            # 如果目标文件夹已存在，先删除
            if os.path.exists(target_path):
                shutil.rmtree(target_path)
            shutil.copytree(source_path, target_path)
            print(f"已复制文件夹: {source_path} -> {target_path}")
        else:
            print(f"未知类型的条目: {source_path}")
            return False
        return True
    except Exception as e:
        print(f"复制条目失败 {source_path}: {str(e)}")
        return False


def main():
    # 1. 选择Excel文件
    excel_path = select_excel_file()
    if not excel_path:
        return

    # 2. 加载映射表（从Sheet2）
    mapping_dict = load_mapping(excel_path, mapping_sheet="Sheet2")

    # 3. 获取并选择sheet（排除映射表）
    all_sheets = get_sheets(excel_path)
    if not all_sheets:
        messagebox.showerror("错误", "Excel文件中没有找到任何sheet")
        return

    # 排除映射表sheet
    exclude_sheets = ["Sheet2"]  # 可以根据需要添加更多要排除的sheet
    selected_sheets = select_sheets(all_sheets, exclude=exclude_sheets)
    if not selected_sheets:
        return

    # 4. 选择目标文件夹
    target_folder = select_target_folder()
    if not target_folder:
        return

    # 5. 创建目标文件夹（如果不存在）
    os.makedirs(target_folder, exist_ok=True)

    # 6. 处理每个选中的sheet
    all_matched_entries = []
    unmatched_reports = []  # 存储未匹配的报表名称

    for sheet_name in selected_sheets:
        try:
            df = pd.read_excel(excel_path, sheet_name=sheet_name)

            # 检查必要的列是否存在
            required_columns = ["上报区划", "报表名称", "原表序号", "总表序号"]
            missing_columns = [col for col in required_columns if col not in df.columns]

            if missing_columns:
                messagebox.showwarning("警告",
                                       f"Sheet '{sheet_name}' 缺少必要的列: {', '.join(missing_columns)}")
                continue

            # 处理每一行数据
            for index, row in df.iterrows():
                original_path = str(row["上报区划"]).strip()
                report_name = str(row["报表名称"]).strip()
                serial_number = row["原表序号"]  # 获取原表序号字段
                total_number = row["总表序号"]

                if not original_path or not report_name:
                    continue

                # 应用映射
                source_path = apply_mapping(original_path, mapping_dict)
                print(f"原始路径: {original_path} -> 映射后路径: {source_path}")

                # 检查源路径是否存在
                if not os.path.exists(source_path):
                    print(f"路径不存在: {source_path} (原始路径: {original_path})")
                    unmatched_reports.append(f"{report_name} (路径不存在: {source_path})")
                    continue

                # 查找匹配的条目（文件或文件夹）
                matched_entry = find_first_entry(source_path, report_name, serial_number)

                if matched_entry:
                    all_matched_entries.append(matched_entry)

                    # 复制条目到目标文件夹
                    try:
                        # 获取原始条目名称
                        original_entry_name = os.path.basename(matched_entry)

                        # 确保总表序号不是NaN
                        if pd.isna(total_number):
                            base_name = original_entry_name
                        else:
                            # 添加总表序号前缀
                            base_name = f"{total_number}_{original_entry_name}"

                        # 构建目标路径
                        target_path = os.path.join(target_folder, base_name)

                        # 如果目标条目已存在，添加计数器
                        counter = 1
                        while os.path.exists(target_path):
                            name, ext = os.path.splitext(base_name)
                            if os.path.isfile(matched_entry):
                                target_name = f"{name}_{counter}{ext}"
                            else:
                                target_name = f"{name}_{counter}"
                            target_path = os.path.join(target_folder, target_name)
                            counter += 1

                        # 复制条目（文件或文件夹）
                        if copy_entry(matched_entry, target_path):
                            print(f"已复制: {matched_entry} -> {target_path}")
                        else:
                            print(f"复制失败: {matched_entry}")

                    except Exception as e:
                        print(f"复制条目失败 {matched_entry}: {str(e)}")
                else:
                    # 记录未匹配的报表名称
                    unmatched_reports.append(f"{report_name} (序号: {serial_number})")

        except Exception as e:
            messagebox.showerror("处理错误", f"处理sheet '{sheet_name}'时出错: {str(e)}")

    # 显示完成信息
    message = f"处理完成!\n\n共找到并复制了 {len(all_matched_entries)} 个条目\n到目录: {target_folder}"

    if unmatched_reports:
        message += f"\n\n有 {len(unmatched_reports)} 个报表未找到匹配条目"
        # 显示未匹配报表窗口
        show_unmatched_report(unmatched_reports)

    messagebox.showinfo("完成", message)


if __name__ == "__main__":
    main()